<?php

namespace App\Console\Commands;

use Carbon\Carbon;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\Log;
use DB;

class CalculateDaily extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'calculate_daily';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '每天统计所有数据';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        try {
            DB::transaction(function (){
                $start_time =  Carbon::yesterday()->subDays(0)->startOfDay()->timestamp;
                $end_time = Carbon::yesterday()->subDays(0)->endOfDay()->timestamp;
                $companies = DB::table('company')->select('id','company_name','amount','order_volume','user_number','order_money','cost')->get();

                $orders = DB::table('order')->select('id','send_time','order_money','send_money','company_id')
                    ->selectRaw('(SELECT SUM(ROUND(inventory_price*sort_num,2)) as cost FROM '.config('database.connections.mysql.prefix').'order_list WHERE '.config('database.connections.mysql.prefix').'order_list.oid='.config('database.connections.mysql.prefix').'order.id) as cost')
                    ->whereIn('order_status',[2,3])->whereBetween('send_time',[$start_time,$end_time])->get();
                $orders_for_company = $orders->groupBy('company_id');
                foreach ($companies as $company) {
                    if (!isset($orders_for_company[$company->id])) continue;
                    $this->calculateAmount($orders_for_company[$company->id],$company);
                }
                if (!empty($orders->pluck('id'))) {
                    $order_list = DB::table('order_list')->select('id','standard_goods_id','standard_spu_id','sort_num')
                        ->selectRaw("ROUND(sort_num*sort_price,2) as sort_money")
                        ->where('standard_goods_id','>',0)->where('standard_spu_id','>',0)
                        ->whereIn('oid',$orders->pluck('id'))
                        ->get()->groupBy('standard_spu_id')->transform(function ($item,$k){
                            $goods_spu = DB::table('statistics_goods')->where('goods_spu_id',$k)->first();
                            if (empty($goods_spu)) {
                                $data = [
                                    'goods_id' => $item->first()->standard_goods_id,
                                    'goods_spu_id' => $k,
                                    'amount' => round($item->sum('sort_money'),2),
                                    'sales' => round($item->sum('sort_num'),2)
                                ];
                                return $data;
                            } else {
                                DB::table('statistics_goods')->where('id',$goods_spu->id)->update([
                                    'amount' => $goods_spu->amount + round($item->sum('sort_money'),2),
                                    'sales' => $goods_spu->sales + round($item->sum('sort_num'),2),
                                ]);
                                return [];
                            }
                        })->toArray();
                    $order_list = array_filter($order_list);
                    if (!empty($order_list)) {
                        DB::table('statistics_goods')->insert($order_list);
                    }
                }
            },5);
        } catch (\Exception $e) {
            Log::info('calculate_daily:company error',['msg'=>$e->getMessage(),'trace'=>$e->getTrace()]);
        }
    }

    protected function calculateAmount($orders,$company)
    {
        $user_number = DB::table('member')->select('id')->where('status',1)->where('is_del',0)->where('company_id',$company->id)->count();
        $send_time = $orders->first()->send_time;
        $amount = round($orders->sum('send_money'),2);
        $order_money = round($orders->sum('order_money'),2);
        $cost = round($orders->sum('cost'),2);
        $order_volume = $orders->count();
        $days = [
            'dates' => date("Y-m-d",$send_time),
            'timestamps' => $send_time,
            'amount' => $amount,
            'order_volume' => $order_volume,
            'user_number' => $user_number,
            'company_id' => $company->id,
            'order_money' => $order_money,
            'cost' => $cost
        ];

        DB::table('statistics_customer_day')->insert($days);

        $month = date("Y-m",$send_time);
        $month_info = DB::table('statistics_customer_month')->where(['dates'=>$month,'company_id'=>$company->id])->first();
        if (!empty($month_info)) {
            DB::table('statistics_customer_month')->where('id',$month_info->id)->update([
                'amount' => $month_info->amount+$amount,
                'order_volume' => $month_info->order_volume+$order_volume,
                'user_number' => $user_number,
                'order_money' => $month_info->order_money + $order_money,
                'cost' => $month_info->cost + $cost
            ]);
        } else {
            $months = [
                'dates' => $month,
                'timestamps' => strtotime($month.'-01'),
                'amount' => $amount,
                'order_volume' => $order_volume,
                'user_number' => $user_number,
                'company_id' => $company->id,
                'order_money' => $order_money,
                'cost' => $cost
            ];
            DB::table('statistics_customer_month')->insert($months);
        }

        $year = date("Y",$send_time);
        $year_info = DB::table('statistics_customer_year')->where(['dates'=>$year,'company_id'=>$company->id])->first();
        if (!empty($year_info)) {
            DB::table('statistics_customer_year')->where('id',$year_info->id)->update([
                'amount' => $year_info->amount+$amount,
                'order_volume' => $year_info->order_volume+$order_volume,
                'user_number' => $user_number,
                'order_money' => $year_info->order_money + $order_money,
                'cost' => $year_info->cost + $cost
            ]);
        } else {
            $years = [
                'dates' => $year,
                'timestamps' => strtotime($year.'-01-01'),
                'amount' => $amount,
                'order_volume' => $order_volume,
                'user_number' => $user_number,
                'company_id' => $company->id,
                'order_money' => $order_money,
                'cost' => $cost
            ];
            DB::table('statistics_customer_year')->insert($years);
        }

        $amount_all = $company->amount + $amount;
        $order_volume_all = $company->order_volume + $order_volume;
        $order_money_all = $company->order_money + $order_money;
        $cost_all = $company->cost + $cost;

        DB::table('company')->where('id',$company->id)->update([
            'amount' => $amount_all,
            'order_volume' => $order_volume_all,
            'user_number' => $user_number,
            'order_money' => $order_money_all,
            'cost' => $cost_all
        ]);

        return true;
    }
}
